package cn.doitedu.datayi.data_service.service;

import cn.doitedu.datayi.data_service.dao.PlatformMetaDao;
import cn.doitedu.datayi.data_service.dao.PrestoDao;
import cn.doitedu.datayi.data_service.po.FunnelStatistic;
import cn.doitedu.datayi.data_service.vo.FunnelDefineVo;
import cn.doitedu.datayi.data_service.vo.FunnelStep;
import cn.doitedu.datayi.data_service.vo.StepProperty;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
public class YieePlatFormPrestoServiceImpl implements YieePlatFormPrestoService {
    @Autowired
    PlatformMetaDao platformMetaDao;

    @Autowired
    PrestoDao prestoDao;


    @Override
    public ArrayList<FunnelStatistic> funnelQuery(FunnelDefineVo vo) throws Exception {

        /**
         * with tmp as (select guid,concat_ws(',',sort_array(collect_list(timestamp || eventid )))as evs from hive.dwd.event_app_detail where (dt='2021-01-11') and  (
         *    (eventid='n5' and properties['7']='6') or (eventid='x0' and properties['14']='6') or (eventid='a0' and properties['7']='6')
         * ) group by guid )
         */
        String with1 = String.format("with tmp as (select guid,concat_ws(',',array_sort(array_agg(cast(timestamp as varchar) || eventid ))) as evs from hive.dwd.event_app_detail where (dt='%1$s') and  ( ",vo.getDt());

        List<FunnelStep> steps = vo.getSteps();

        ArrayList<String> tmp = new ArrayList<>();
        for (FunnelStep step : steps) {

            ArrayList<String> params = new ArrayList<>();
            params.add(String.format("eventid='%1$s'",step.getEventId()));
            List<StepProperty> propertyParams = step.getPropertyParams();
            for (StepProperty p : propertyParams) {
                params.add(String.format("properties['%1$s']='%2$s'",p.getPropertyName(),p.getCompareValue()));
            }
            String stp = "( " + StringUtils.join(params," and ") +")";
            tmp.add(stp);
        }

        String with2 = StringUtils.join(tmp, " or ");

        String with3 = ") group by guid ) ";

        /**
         * select guid, case
         *
         *  when regexp_extract(evs,'.*?  (n5).*?(x0).*?(a0).*?    ',3)='a0' then 3
         *  when regexp_extract(evs,'.*?(n5).*?(x0).*?',2)='x0' then 2
         *  when regexp_extract(evs,'.*?(n5).*?',1)='n5' then 1
         *
         * else 0 end as max_step from tmp ;
         */
        String select1 = "select guid, case  ";

        StringBuilder whenSb = new StringBuilder();
        for(int i=0;i<steps.size();i++){
            String left = " when regexp_extract(evs,'.*?";
            StringBuilder middleSb = new StringBuilder();
            for(int j=0;j<steps.size()-i;j++){
                middleSb.append(String.format("(%1$s).*?",steps.get(j).getEventId()));
            }
            String right = String.format("',%1$s)='%2$s' then %1$s ",steps.size()-i,steps.get(steps.size()-i-1).getEventId());
            whenSb.append(left+middleSb.toString()+right);
        }

        String select2 = whenSb.toString();


        String select3 = " else 0 end as max_step from tmp ";


        String selectOuter = "select   count(if(max_step>=1,1,null)) as step1_cnt, count(if(max_step>=2,1,null)) as step2_cnt, count(if(max_step>=3,1,null)) as step3_cnt from ( ";
        String outerEnd = " ) o";


        String sql = with1+with2+with3+selectOuter+select1+select2+select3+outerEnd;

        ArrayList<FunnelStatistic> funnelPoList = prestoDao.queryFunnel(sql);


        return funnelPoList;
    }



    @Override
    public boolean saveFunnelDefinition(FunnelDefineVo vo) throws Exception {

        return platformMetaDao.saveFunnelDefinition(vo);
    }
}
